﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using DAO.G02TravelWebService;
/// <summary>
/// Summary description for DataProvider
/// </summary>

public class DataProvider
{
    private static SqlConnection _con = null;    

    static DataProvider()
    {
        //Get connection string from web.conf or app.conf
        string strConnection = ConfigurationManager.ConnectionStrings["AgentDB"].ConnectionString;
        _con = new SqlConnection(strConnection);        
    }

    public static DataTable ExecuteQuery(string strQuery)
    {
        DataTable resTable = new DataTable();

        try
        {
            _con.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(strQuery, _con);

            adapter.Fill(resTable);
        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally
        {
            _con.Close();
        }

        return resTable;
    }

    public static int ExecuteNoneQuery(String query)
    {
        int n;
        try
        {

            SqlConnection connect = DataProvider._con;
            connect.Open();
            try
            {
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = query;
                n = command.ExecuteNonQuery();
            }

            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connect.Close();
            }
        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi lenh DML: " + ex.Message);
        }

        return n;
    }

    public static DataTable ExecuteStoreProc(string storeProcName, List<string> arrParameterName, ArrayList arrParameterValue)
    {
        DataTable resTable = new DataTable(); 

        try
        {
            _con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = storeProcName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = _con;
            if (arrParameterName != null)
            {
                //Lay so parameter
                int N = arrParameterName.Count;
                for (int i = 0; i < N; i++)
                {
                    SqlParameter sqlParam = new SqlParameter(arrParameterName[i], arrParameterValue[i]);
                    cmd.Parameters.Add(sqlParam);
                }
            }            

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(resTable);

            _con.Close();
        }
        catch (Exception ex)
        {
            _con.Close();
            throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
        }

        return resTable;
    }
}
